package com.mlt.back.dao.impl;

import com.mlt.back.beans.Good;
import com.mlt.back.dao.GoodDao;
import com.mlt.back.utils.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/**
 *@author 李
 *@date 2022/12/26
 *@param
 *@return
*/
public class GoodDaoImpl implements GoodDao {

    @Override
    public List<Good> selectAll() {

        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;

        try {
            conn= DBUtil.getConnection();
            String sql="select * from t_good";
            ps=conn.prepareStatement(sql);
            rs=ps.executeQuery();

            Good good = null;
            List<Good> goods = new ArrayList<>();
            while (rs.next()){

                good = new Good();
                good.setGood_id(rs.getInt("good_id"));
                good.setGood_name(rs.getString("good_name"));
                good.setGood_img(rs.getString("good_img"));
                good.setGood_min_price(rs.getInt("good_min_price"));
                good.setGood_cost(rs.getInt("good_cost"));

                goods.add(good);

            }
            return goods;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(conn,ps,rs);
        }
        return null;
    }

    @Override
    public List<Good> selectByCategory(String categoryId) {

        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        //编写sql语句
        try {
            conn= DBUtil.getConnection();
            String sql="select d.* from t_category_brand a " +
                    "left join t_brand b on b.brand_id = a.fk_brand_id " +
                    "left join t_brand_good c on c.fk_brand_id = b.brand_id " +
                    "left join t_good d on d.good_id = c.fk_good_id " +
                    "where a.fk_category_id = ? and d.good_id is not null";
            ps=conn.prepareStatement(sql);
            ps.setInt(1, Integer.parseInt(categoryId));
            rs=ps.executeQuery();

            Good good = null;
            List<Good> goods = new ArrayList<>();
            while (rs.next()){

                good = new Good();
                good.setGood_id(rs.getInt("good_id"));
                good.setGood_name(rs.getString("good_name"));
                good.setGood_img(rs.getString("good_img"));
                good.setGood_min_price(rs.getInt("good_min_price"));
                good.setGood_cost(rs.getInt("good_cost"));

                goods.add(good);

            }
            return goods;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(conn,ps,rs);
        }

        return null;

    }
}
